Arbeitspaket (AP) 3: Management & Nutzung Räumliche Daten¶
Angaben Studierende(r) (fehlende Angaben ergänzen)¶
| Vorname: | Silas |
| Nachname: | Häffner |
| Immatrikulationsnummer: | 24672354 |
| Modul: | Data Science |
| Prüfungsdatum / Raum / Zeit: | 07.10.2024 / Raum: SF O3.54 / 8:00 – 11:45 |
| Erlaubte Hilfsmittel: | w.MA.XX.DS.24HS (Data Science) Open Book, Eigener Computer, Internet-Zugang |
| Nicht erlaubt: | Nicht erlaubt ist der Einsatz beliebiger Formen von generativer KI (z.B. Copilot, ChatGPT) sowie beliebige Formen von Kommunikation oder Kollaboration mit anderen Menschen. |
Bewertungskriterien¶
(max. erreichbare Punkte: 48)¶
| Kategorie | Beschreibung | Punkteverteilung |
|---|---|---|
| Code nicht lauffähig oder Ergebnisse nicht sinnvoll | Der Code enthält Fehler, die verhindern, dass er ausgeführt werden kann (z.B. Syntaxfehler) oder es werden Ergebnisse ausgegeben, welche nicht zur Fragestellung passen. | 0 Punkte |
| Code lauffähig, aber mit gravierenden Mängeln | Der Code läuft, aber die Ergebnisse sind aufgrund wesentlicher Fehler unvollständig (z.B. fehlende Joins, gravierende Fehler in SQL-Abfragen). Nur geringer Fortschritt erkennbar. | 25% der max. erreichbaren Punkte |
| Code lauffähig, aber mit mittleren Mängeln | Der Code läuft und liefert teilweise korrekte Ergebnisse, aber es gibt grössere Fehler (z.B. fehlende Spalten, unvollständige SQL-Abfragen). Die Ergebnisse sind nachvollziehbar, aber unvollständig oder ungenau. | 50% der max. erreichbaren Punkte |
| Code lauffähig, aber mit minimalen Mängeln | Der Code läuft und liefert ein weitgehend korrektes Ergebnis, aber kleinere Fehler (z.B. falsche oder fehlende Sortierung, Rundung von Werten falsch) beeinträchtigen die Vollständigkeit des Ergebnisses. | 75% der max. erreichbaren Punkte |
| Code lauffähig und korrekt | Der Code läuft einwandfrei und liefert das korrekte Ergebnis ohne Mängel. | 100% der max. erreichbaren Punkte |
Python Libraries und Settings¶
In [39]:
# Libraries
import os
import folium
import pandas as pd
import geopandas as gpd
from sqlalchemy import create_engine, text
# Ignore warnings
import warnings
warnings.filterwarnings("ignore")
print(os.getcwd())
/workspaces/python_postgresql_postgis
Vorbereitung (Hinweis: dieser Teil wird nicht bewertet)¶
1.) Starten Sie eine GitHub Codespaces Instanz auf Basis Ihres Forks des folgenden GitHub Repositories:¶
GitHub-Repository: https://github.com/mario-gellrich-zhaw/python_postgresql_postgis¶
WICHTIG!!! Verwenden Sie eine GitHub Codespaces Instanz mit ausreichend Arbeitsspeicher (4core, 16GB RAM).¶
Hinweis:
- Im Unterricht wurden bereits sämtliche Installationen und Einstellungen inkl. der Registrierung des Datenbank Servers auf pgAdmin vorgenommen.
- Falls Sie die Codespaces-Instanz neu erstellen müssen, folgen Sie bitte den detaillierten Erklärungen auf der README-Seite des GitHub Repositories.
2.) Erstellen und Testen Sie die Datenbankverbindung mit der 'osm_switzerland' Datenbank.¶
In [40]:
# Set up Database Connection
user = "pgadmin"
password = "geheim"
host = "localhost"
port = "5432"
database = "osm_switzerland"
# Erstellen der Connection URL
db_connection_url = "postgresql://" + user + ":" + password +\
"@" + host + ":" + port + "/" + database
# Erstellen SQLAlchemy Engine
engine = create_engine(db_connection_url)
# Test der Connection
with engine.connect() as connection:
result = connection.execute(text('SELECT current_database()'))
print(result.fetchone())
# Verbindung trennen
engine.dispose()
('osm_switzerland',)
Aufgaben (Dieser Teil wird bewertet!)¶
Hinweise zu den folgenden Aufgabenstellungen:
- In diesem Jupyter Notebook gibt es jeweils zwei Code-Zellen pro Aufgabe:
- Eine Codezelle mit Python-Code und einem SQL-Statement für die Datenbank-Abfrage.
- Eine Codezelle mit Python-Code für die Kartendarstellung der Ergebnisse der jeweiligen SQL-Abfrage.
- In den Codezellen für die Datenbank-Abfrage muss jeweils das SQL-Statement ergänzt werden.
- In den Codezellen für die Kartendarstellung muss nur dann der Python Code ergänzt werden, wenn in der Aufgabe danach gefragt wird.
Aufgabe (1): Erstellen Sie eine Abfrage sämtlicher Autoreparatur-Werkstätten in der Schweiz¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in der Tabelle 'planet_osm_point'.
- Stellen sie in der Ergebnistabelle die Spalten: osm_id, shop sowie die transformierte Geometrie als Spalte geom dar.
- Tipp: Die Geometry wird mit Hilfe der Funktion st_transform() transformiert, z.B.: st_transform(p.way, 4326) AS geom.
- Tipp: Autoreparatur-Werkstätten sind mit dem key:value Paar shop='car_repair' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 6)
In [41]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
h.osm_id,
h.shop,
h.name,
ST_Transform(h.way, 4326) AS geom
FROM planet_osm_point h
WHERE h.shop = 'car_repair'
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[41]:
| osm_id | shop | name | geom | |
|---|---|---|---|---|
| 0 | 1811755810 | car_repair | Grenzgarage | POINT (9.62898 47.45412) |
| 1 | 9408250312 | car_repair | Gebr. Wirth AG | POINT (9.63098 47.45327) |
| 2 | 5254765356 | car_repair | Richner AG | POINT (9.43594 47.50471) |
| 3 | 9408188280 | car_repair | Zehender Garage AG | POINT (9.46826 47.49408) |
| 4 | 3346119599 | car_repair | Garage Martino GmbH | POINT (9.49096 47.47737) |
| ... | ... | ... | ... | ... |
| 1458 | 7524279282 | car_repair | None | POINT (9.15641 47.54915) |
| 1459 | 7523837743 | car_repair | None | POINT (9.18904 47.55991) |
| 1460 | 4366881989 | car_repair | Autoviva VW und Seat | POINT (9.27967 47.54715) |
| 1461 | 1493634725 | car_repair | Morandi Group AG | POINT (9.19848 47.63967) |
| 1462 | 1485836431 | car_repair | Toyota Garage Schlauri AG | POINT (9.16553 47.64061) |
1463 rows × 4 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [42]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=8,
tiles='CartoDB positron')
# Map settings
folium.GeoJson(
gdf,
name='map'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[42]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (2) Erstellen Sie eine Abfrage aller Biergärten in der Schweiz.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in den Tabellen 'planet_osm_point'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, amenity, name und die transformierte Geometrie als Spalte geom dar.
- Tipp: Biergärten sind mit dem key:value Paar amenity='biergarten' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 6)
In [43]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
h.osm_id,
h.amenity,
h.shop,
h.name,
ST_Transform(h.way, 4326) AS geom
FROM planet_osm_point h
WHERE h.amenity = 'biergarten'
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf.head()
Out[43]:
| osm_id | amenity | shop | name | geom | |
|---|---|---|---|---|---|
| 0 | 704467869 | biergarten | None | Bierhalle | POINT (9.6068 47.40694) |
| 1 | 370319479 | biergarten | None | Center da Surf | POINT (9.79135 46.45518) |
| 2 | 2467575051 | biergarten | None | Le Bioley | POINT (7.12275 46.09499) |
| 3 | 746772927 | biergarten | None | Buvette de l'alpage du col du Lein | POINT (7.15997 46.11045) |
| 4 | 370416485 | biergarten | None | Bar des Etablons | POINT (7.23621 46.14038) |
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [44]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=8,
tiles='CartoDB positron')
# Map settings
folium.GeoJson(
gdf,
name='map'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[44]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (3): Erstellen Sie eine Abfrage aller Gebäude in der Stadthausstrasse in Winterthur, welche vollständige Adressangaben besitzen.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in der Tabelle 'planet_osm_polygon'.
- Vollständige Adressangabe bedeutet: Strassenname, Haunummer, PLZ, Gemeindename sind vorhanden.
- Stellen Sie in der Ergebnistabelle sämtliche Adressangaben sowie die transformierte Geometrie als Spalte geom dar.
- Verwenden Sie für die Darstellung als Hintergrundkarte ein Satellitenbild (ESRIWorldImagery) als maptile.
- Tipp: Gebäude sind in der Spalte 'building' klassifiziert. Mit WHERE building IS NOT NULL können Sie Gebäude filtern.
(max. erreichbare Punkte: 6)
In [45]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
p.osm_id,
p."addr:street",
p."addr:housenumber",
p."addr:city",
p."addr:postcode",
p.building,
st_transform(p.way, 4326) AS geom
FROM
public.planet_osm_polygon AS p
WHERE
p."addr:street" IS NOT NULL
AND p."addr:housenumber" IS NOT NULL
AND p."addr:city" IS NOT NULL
AND p."addr:street" IN ('Stadthausstrasse')
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[45]:
| osm_id | addr:street | addr:housenumber | addr:city | addr:postcode | building | geom | |
|---|---|---|---|---|---|---|---|
| 0 | 9264543 | Stadthausstrasse | 4a | Winterthur | 8400 | government | POLYGON ((8.73111 47.50115, 8.73112 47.50085, ... |
| 1 | 22301937 | Stadthausstrasse | 4 | Winterthur | 8400 | apartments | POLYGON ((8.73232 47.50109, 8.73233 47.50107, ... |
| 2 | 75027485 | Stadthausstrasse | 31 | Winterthur | 8400 | office | POLYGON ((8.73162 47.50041, 8.73165 47.50029, ... |
| 3 | 75027472 | Stadthausstrasse | 35 | Winterthur | 8400 | apartments | POLYGON ((8.73153 47.5004, 8.73157 47.50026, 8... |
| 4 | 75027503 | Stadthausstrasse | 37 | Winterthur | 8400 | apartments | POLYGON ((8.73146 47.5004, 8.73149 47.50028, 8... |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 71 | 86335426 | Stadthausstrasse | 4 | Luzern | 6003 | apartments | POLYGON ((8.30523 47.04936, 8.30531 47.04926, ... |
| 72 | 86335481 | Stadthausstrasse | 2 | Luzern | 6003 | apartments | POLYGON ((8.30538 47.0494, 8.30545 47.04932, 8... |
| 73 | 86335380 | Stadthausstrasse | 7 | Luzern | 6003 | apartments | POLYGON ((8.30491 47.04927, 8.30502 47.04915, ... |
| 74 | 86335371 | Stadthausstrasse | 6 | Luzern | 6003 | apartments | POLYGON ((8.30509 47.04931, 8.3051 47.04929, 8... |
| 75 | 134980581 | Stadthausstrasse | 10b | Winterthur | 8400 | yes | POLYGON ((8.72652 47.50075, 8.72661 47.50063, ... |
76 rows × 7 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [46]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=17,
tiles='EsriWorldImagery')
# Map settings
folium.Choropleth(
geo_data=gdf,
name='map',
fill_color='greenyellow'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[46]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (4): Erstellen Sie eine Abfrage aller Strassen in der Schweiz, welche als 'motorway' klassifiziert sind.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Informationen in der Tabelle 'planet_osm_roads'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, highway und die transformierte Geometrie als Spalte geom dar.
- Tipp: Motorways sind mit dem key:value Paar highway='motorway' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 6)
In [47]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
r.osm_id,
r.highway,
ST_Transform(r.way, 4326) AS geom
FROM planet_osm_roads r
WHERE r.highway = 'motorway';
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[47]:
| osm_id | highway | geom | |
|---|---|---|---|
| 0 | 1236416666 | motorway | LINESTRING (9.64218 47.43433, 9.6422 47.43402,... |
| 1 | 552469430 | motorway | LINESTRING (9.64232 47.43412, 9.6423 47.43469) |
| 2 | 552469432 | motorway | LINESTRING (9.6423 47.43469, 9.64232 47.43504,... |
| 3 | 552469428 | motorway | LINESTRING (9.64312 47.43793, 9.6429 47.43753,... |
| 4 | 186132194 | motorway | LINESTRING (9.64235 47.43533, 9.6424 47.43568,... |
| ... | ... | ... | ... |
| 8264 | 100501651 | motorway | LINESTRING (9.15834 47.65468, 9.15789 47.65424... |
| 8265 | 14334868 | motorway | LINESTRING (9.15371 47.65075, 9.15343 47.6503,... |
| 8266 | 20822469 | motorway | LINESTRING (9.14594 47.63688, 9.14599 47.63692... |
| 8267 | 70908737 | motorway | LINESTRING (9.14357 47.63107, 9.14364 47.63151) |
| 8268 | 1316916577 | motorway | LINESTRING (9.14365 47.63224, 9.14352 47.63152) |
8269 rows × 3 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [48]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=9,
tiles='CartoDB positron')
# Map settings
folium.Choropleth(
geo_data=gdf,
name='map',
line_weight=3,
line_color='red'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[48]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (5): Erstellen Sie eine Abfrage aller Schweizer Flüsse. Generieren Sie zusätzlich Buffer um die Flüsse mit einer Breite von 2000m.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Informationen in der Tabelle 'planet_osm_line'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, waterway sowie die transformierte Geometrie als Spalte geom dar.
- Tipp: Flüsse sind mit dem key:value Paar waterway='river' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
- Tipp: Per Default wird für jedes Fluss-Segment ein separater Buffer erstellt. Es ist nicht notwendig daraus einen einzelnen Buffer zu generieren.
(max. erreichbare Punkte: 8)
In [49]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
l.osm_id,
l.waterway,
ST_Transform(l.way, 4326) AS geom
FROM planet_osm_line l
WHERE l.waterway = 'river';
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[49]:
| osm_id | waterway | geom | |
|---|---|---|---|
| 0 | 965608814 | river | LINESTRING (9.55554 47.58757, 9.55502 47.58628... |
| 1 | 123157776 | river | LINESTRING (9.6523 47.45459, 9.64581 47.45564,... |
| 2 | 197492692 | river | LINESTRING (9.59407 47.46441, 9.59392 47.46459... |
| 3 | 947314919 | river | LINESTRING (9.56177 47.50449, 9.55057 47.53718... |
| 4 | 1210578598 | river | LINESTRING (9.47697 47.48965, 9.47751 47.49004... |
| ... | ... | ... | ... |
| 2553 | 151698226 | river | LINESTRING (8.87064 47.56425, 8.87076 47.56459) |
| 2554 | 1070540790 | river | LINESTRING (8.87031 47.56279, 8.87036 47.56304) |
| 2555 | 151698225 | river | LINESTRING (8.87036 47.56304, 8.87064 47.56425) |
| 2556 | 145041304 | river | LINESTRING (9.12606 47.66731, 9.11666 47.6692,... |
| 2557 | 514055688 | river | LINESTRING (9.64477 47.52537, 9.63639 47.52762... |
2558 rows × 3 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [50]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=8,
tiles='CartoDB positron')
# Map settings
folium.Choropleth(
geo_data=gdf,
name='map',
fill_color='greenyellow'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[50]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (6): Erstellen Sie eine Abfrage der Bäckerei-Geschäfte in Zürich und Winterthur.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in den Tabellen 'planet_osm_point' (Backereien).
- Verwenden Sie die Städtenamen aus den Adressangaben für die Abfrage der Bäckerei-Standorte (Zürich, Winterthur).
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, "addr:city" sowie die transformierte Geometrie als geom dar.
- Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
- Sortieren Sie die Bäckerei-Geschäfte aufsteigend nach osm_id.
- Tipp: Bäckerei-Geschäfte sind mit dem key:value Paar shop='bakery' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 8)
In [56]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
h.osm_id,
h.shop,
h.name,
h.addr:city,
ST_Transform(h.way, 4326) AS geom
FROM planet_osm_point h
WHERE h."addr:city" = ('Winterthur', 'Zurich')
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
--------------------------------------------------------------------------- SyntaxError Traceback (most recent call last) File ~/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py:1967, in Connection._exec_single_context(self, dialect, context, statement, parameters) 1966 if not evt_handled: -> 1967 self.dialect.do_execute( 1968 cursor, str_statement, effective_parameters, context 1969 ) 1971 if self._has_events or self.engine._has_events: File ~/.local/lib/python3.12/site-packages/sqlalchemy/engine/default.py:941, in DefaultDialect.do_execute(self, cursor, statement, parameters, context) 940 def do_execute(self, cursor, statement, parameters, context=None): --> 941 cursor.execute(statement, parameters) SyntaxError: syntax error at or near ":" LINE 5: h.addr:city, ^ The above exception was the direct cause of the following exception: ProgrammingError Traceback (most recent call last) Cell In[56], line 16 5 sql = """SELECT 6 h.osm_id, 7 h.shop, (...) 12 WHERE h."addr:city" = ('Winterthur') 13 ;""" 15 # Ergebnis in GeoDataFrame abspeichern ---> 16 gdf = gpd.GeoDataFrame.from_postgis(sql, engine) 18 # Datenbankverbindung trennen 19 engine.dispose() File ~/.local/lib/python3.12/site-packages/geopandas/geodataframe.py:817, in GeoDataFrame.from_postgis(cls, sql, con, geom_col, crs, index_col, coerce_float, parse_dates, params, chunksize) 747 @classmethod 748 def from_postgis( 749 cls, (...) 758 chunksize=None, 759 ): 760 """ 761 Alternate constructor to create a ``GeoDataFrame`` from a sql query 762 containing a geometry column in WKB representation. (...) 814 geopandas.read_postgis : read PostGIS database to GeoDataFrame 815 """ --> 817 df = geopandas.io.sql._read_postgis( 818 sql, 819 con, 820 geom_col=geom_col, 821 crs=crs, 822 index_col=index_col, 823 coerce_float=coerce_float, 824 parse_dates=parse_dates, 825 params=params, 826 chunksize=chunksize, 827 ) 829 return df File ~/.local/lib/python3.12/site-packages/geopandas/io/sql.py:185, in _read_postgis(sql, con, geom_col, crs, index_col, coerce_float, parse_dates, params, chunksize) 135 """ 136 Returns a GeoDataFrame corresponding to the result of the query 137 string, which must contain a geometry column in WKB representation. (...) 180 >>> df = geopandas.read_postgis(sql, con) # doctest: +SKIP 181 """ 183 if chunksize is None: 184 # read all in one chunk and return a single GeoDataFrame --> 185 df = pd.read_sql( 186 sql, 187 con, 188 index_col=index_col, 189 coerce_float=coerce_float, 190 parse_dates=parse_dates, 191 params=params, 192 chunksize=chunksize, 193 ) 194 return _df_to_geodf(df, geom_col=geom_col, crs=crs, con=con) 196 else: 197 # read data in chunks and return a generator File ~/.local/lib/python3.12/site-packages/pandas/io/sql.py:734, in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize, dtype_backend, dtype) 724 return pandas_sql.read_table( 725 sql, 726 index_col=index_col, (...) 731 dtype_backend=dtype_backend, 732 ) 733 else: --> 734 return pandas_sql.read_query( 735 sql, 736 index_col=index_col, 737 params=params, 738 coerce_float=coerce_float, 739 parse_dates=parse_dates, 740 chunksize=chunksize, 741 dtype_backend=dtype_backend, 742 dtype=dtype, 743 ) File ~/.local/lib/python3.12/site-packages/pandas/io/sql.py:1836, in SQLDatabase.read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype, dtype_backend) 1779 def read_query( 1780 self, 1781 sql: str, (...) 1788 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy", 1789 ) -> DataFrame | Iterator[DataFrame]: 1790 """ 1791 Read SQL query into a DataFrame. 1792 (...) 1834 1835 """ -> 1836 result = self.execute(sql, params) 1837 columns = result.keys() 1839 if chunksize is not None: File ~/.local/lib/python3.12/site-packages/pandas/io/sql.py:1659, in SQLDatabase.execute(self, sql, params) 1657 args = [] if params is None else [params] 1658 if isinstance(sql, str): -> 1659 return self.con.exec_driver_sql(sql, *args) 1660 return self.con.execute(sql, *args) File ~/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py:1779, in Connection.exec_driver_sql(self, statement, parameters, execution_options) 1774 execution_options = self._execution_options.merge_with( 1775 execution_options 1776 ) 1778 dialect = self.dialect -> 1779 ret = self._execute_context( 1780 dialect, 1781 dialect.execution_ctx_cls._init_statement, 1782 statement, 1783 None, 1784 execution_options, 1785 statement, 1786 distilled_parameters, 1787 ) 1789 return ret File ~/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py:1846, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw) 1844 return self._exec_insertmany_context(dialect, context) 1845 else: -> 1846 return self._exec_single_context( 1847 dialect, context, statement, parameters 1848 ) File ~/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py:1986, in Connection._exec_single_context(self, dialect, context, statement, parameters) 1983 result = context._setup_result_proxy() 1985 except BaseException as e: -> 1986 self._handle_dbapi_exception( 1987 e, str_statement, effective_parameters, cursor, context 1988 ) 1990 return result File ~/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py:2355, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context, is_sub_exec) 2353 elif should_wrap: 2354 assert sqlalchemy_exception is not None -> 2355 raise sqlalchemy_exception.with_traceback(exc_info[2]) from e 2356 else: 2357 assert exc_info[1] is not None File ~/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py:1967, in Connection._exec_single_context(self, dialect, context, statement, parameters) 1965 break 1966 if not evt_handled: -> 1967 self.dialect.do_execute( 1968 cursor, str_statement, effective_parameters, context 1969 ) 1971 if self._has_events or self.engine._has_events: 1972 self.dispatch.after_cursor_execute( 1973 self, 1974 cursor, (...) 1978 context.executemany, 1979 ) File ~/.local/lib/python3.12/site-packages/sqlalchemy/engine/default.py:941, in DefaultDialect.do_execute(self, cursor, statement, parameters, context) 940 def do_execute(self, cursor, statement, parameters, context=None): --> 941 cursor.execute(statement, parameters) ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near ":" LINE 5: h.addr:city, ^ [SQL: SELECT h.osm_id, h.shop, h.name, h.addr:city, ST_Transform(h.way, 4326) AS geom FROM planet_osm_point h WHERE h."addr:city" = ('Winterthur') ;] (Background on this error at: https://sqlalche.me/e/20/f405)
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [52]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=12,
tiles='ESRIWorldImagery')
# Map settings
folium.GeoJson(
gdf,
name='map',
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[52]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (7): Erstellen Sie eine Abfrage sämtlicher Coiffeur-Geschäfte in einem Radius von 500m um den Hauptbahnhof in Zürich.¶
Details zur Aufgabenstellung:
- Sie finden die Daten in der Tabelle 'planet_osm_point'.
- Berechnen Sie in der Abfrage die Distanz jedes Coiffeur-Geschäfts zum Hauptbahnhof in Metern als Spalte 'distance_meters'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, distance_meters, sowie die transformierte Geometrie als geom dar.
- Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
- Integrieren Sie in die Kartendarstellung den Namen (Spalte 'name') der Coiffeur-Geschäfte als Popup.
- Tipp: Coiffeur-Geschäfte sind mit dem key:value Paar shop='hairdresser' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 8)
In [53]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
p.osm_id,
p.shop,
p.name,
ST_Distance(
ST_Transform(p.way, 4326)::geography,
-- Central station coordinates
ST_SetSRID(ST_MakePoint(8.72397, 47.50031), 4326)::geography
) AS distance_meters,
ST_TRANSFORM(p.way, 4326) AS geom
FROM
planet_osm_point AS p
WHERE
p.shop = 'hairdresser'
AND ST_DWithin(
ST_Transform(p.way, 4326)::geography,
-- Central station coordinates
ST_SetSRID(ST_MakePoint(8.72397, 47.50031), 4326)::geography,
500
)
ORDER BY distance_meters;
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf.head()
Out[53]:
| osm_id | shop | name | distance_meters | geom | |
|---|---|---|---|---|---|
| 0 | 3884033858 | hairdresser | TopHair/BRAUN Hair | 104.579231 | POINT (8.72315 47.50107) |
| 1 | 599561752 | hairdresser | Orinad | 162.173997 | POINT (8.72525 47.49914) |
| 2 | 771204398 | hairdresser | Gidor | 175.756061 | POINT (8.72552 47.50149) |
| 3 | 4125136752 | hairdresser | Coiffeur Fernando | 212.213655 | POINT (8.72138 47.50106) |
| 4 | 703431929 | hairdresser | Haarpalast Sagarra | 217.380437 | POINT (8.72683 47.50007) |
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [54]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=16,
tiles='ESRIWorldImagery')
# Map settings
folium.GeoJson(
gdf,
name='map'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[54]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Jupyter notebook --footer info-- (please always provide this at the end of each notebook)¶
In [55]:
import os
import platform
import socket
from platform import python_version
from datetime import datetime
print('-----------------------------------')
print(os.name.upper())
print(platform.system(), '|', platform.release())
print('Datetime:', datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
print('Python Version:', python_version())
print('IP Address:', socket.gethostbyname(socket.gethostname()))
print('-----------------------------------')
----------------------------------- POSIX Linux | 6.5.0-1025-azure Datetime: 2024-10-07 09:36:47 Python Version: 3.12.1 IP Address: 127.0.0.1 -----------------------------------